ALTER proc sp_insert_lineItem_proc
(
	@salesOrder_id 	int, 
	@lineItem_no 	int, 
	@product_id 	int, 
	@lineItem_Desc 	varchar(500), 
	@qty 		int, 
	@unit_price 	float,
	@amount 	float, 
	@requested_date datetime, 
	@user_id 	varchar(50), 
	@is_taxable 	int, 
	@po_lineItem_no int, 
	@is_only_new_lineitem_change int, 
	@status 	int, 
	@is_expedite 	int,
	@quotation_lineitem_id int,
	@customer_partnumber varchar(50),
	@IS_NEW_CUSTOMER_PART_NUMBER INT, --0 -TRUE 1 -FALSE
	@COOL_CUSTOM_PRICE_FIELD1 float, 
	@COOL_CUSTOM_TEXT_FIELD1 varchar(1000)
	
) 
 
AS
begin
	DECLARE @lineItem_id int
	DECLARE @max_ch_no int
	DECLARE @CUSTOMER_ID INT
	
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM SALES_ORDER WHERE SALESORDER_ID = @salesOrder_id
	
	IF ( @IS_NEW_CUSTOMER_PART_NUMBER IS NOT NULL AND @IS_NEW_CUSTOMER_PART_NUMBER = 0 AND LEN(LTRIM(RTRIM(@customer_partnumber))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) CNT FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @product_id AND CUSTOMER_PARTNUMBER = LTRIM(RTRIM(@customer_partnumber)) AND ODM_ID IS NULL) = 0 )
		 BEGIN
			INSERT INTO CUSTOMER_PARTNUMBERS( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER ) VALUES( @CUSTOMER_ID, @product_id, LTRIM(RTRIM(@customer_partnumber)), '' )
	 	 END
	 END

	INSERT INTO Customer_Open_Order_LineItem ( SalesOrder_ID, COOL_Line_Item_No, Product_ID, COOL_Description, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, COOL_Booking_Date, 
 	COOL_Status, COOL_Added_By_ID, COOL_Added_By_Name,COOL_Taxable , COOL_PO_Line_Item_No, TimeStamp , COOL_Is_Expedite , COOL_Act_Req_Date, Quotation_LineItem_ID, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 )
 	VALUES ( @salesOrder_id, @lineItem_no, @product_id, @lineItem_Desc, @qty, @unit_price, @amount, @requested_date, CURRENT_TIMESTAMP,'HOLD', @user_id, @user_id, @is_taxable , @po_lineItem_no, CURRENT_TIMESTAMP , @is_expedite , @requested_date, @quotation_lineitem_id, @CUSTOMER_PARTNUMBER, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1 )
 
 	
 	SELECT @lineItem_Id = (SELECT @@IDENTITY)
 
 	/*
 	 * For inserting into Change_Sales_Order table, 
 	 * when only new line item is inserted in an existing sales order.
 	 * @is_only_new_lineitem_change = 0 indicates a new line item is being inserted for a new sales order. so not inserted in Change_Sales_Order
 	 * @is_only_new_lineitem_change = 1 or 2 indicates a new line item is inserted to an existing sales order.
 	 * 2 means there is also another change in the sales order, so there is a record inserted in the Change_Sales_Order. so, ChangeNo is not incremented.
 	 * 1 means no change is there apart from new line item insertion. so, ChangeNo is incremented and a row is inserted into Change_Sales_Order.
 	 */
 	
 	SELECT @max_ch_no = isnull(max(changeno),0) from change_sales_order where salesorder_id = @salesOrder_id	
  	if ( @is_only_new_lineitem_change <> 0 and @is_only_new_lineitem_change is not null )
 	begin
 		if (@is_only_new_lineitem_change = 1 and @status = 1)
 		begin
 			set @max_ch_no = @max_ch_no + 1

 			INSERT INTO Change_Sales_Order(SalesOrder_ID,ChangeNo,Customer_ID,CSO_Customer_PO_Number,CSO_Contact_Person,
 			CSO_Sales_Person,CSO_Shipping_Method,CSO_Shipping_Account,CSO_Billing_Address_ID,CSO_Shipping_Address_ID,
 			CSO_Comments,CSO_Shipping_Instructions,CSO_EndUser,CSO_Status,CSO_Booking_Date,CSO_Is_Sample,CSO_Is_Cancel,
 			CSO_Is_Pending,CSO_Is_Shipped,CSO_Entered_By_ID,CSO_Entered_By_Name,CSO_Sales_Tax_Type,CSO_Sub_Total,
 			CSO_Sales_Tax,CSO_Frieght_Tax,CSO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 			CSO_Misc_Name,CSO_Show_Pro_Forma_Invoice,CSO_Reason, CSO_Sales_Rep)
 	
 			(SELECT SalesOrder_ID,@max_ch_no,Customer_ID,SO_Customer_PO_Number,SO_Contact_Person,
 			SO_Sales_Person,SO_Shipping_Method,SO_Shipping_Account,SO_Billing_Address_ID,SO_Shipping_Address_ID,
 			SO_Comments,SO_Shipping_Instructions,SO_EndUser,SO_Status,SO_Booking_Date,SO_Is_Sample,SO_Is_Cancel,
 			SO_Is_Pending,SO_Is_Shipped,SO_Entered_By_ID,SO_Entered_By_Name,SO_Sales_Tax_Type,SO_Sub_Total,
 			SO_Sales_Tax,SO_Frieght_Tax,SO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 			SO_Misc_Name,SO_Show_Pro_Forma_Invoice,null, SO_Sales_Rep
 			FROM Sales_Order WHERE SalesOrder_id = @salesorder_id)
 
 		end
 		
 	end
 
	 /* If this is first line item then it will have duplicate copy into CCOL*/
	INSERT INTO Customer_Change_Order_LineItem (LineItem_ID,SalesOrder_ID,ChangeNo,CCO_Line_Item_No, Product_ID, 
	CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Booking_Date, CCO_Status, CCO_Change_By_ID, CCO_Change_By_Name,CCO_Taxable,CCO_PO_Line_Item_No, TimeStamp, Quotation_LineItem_ID, CCO_CUSTOMER_PARTNUMBER, CCO_CUSTOM_PRICE_FIELD1, CCO_CUSTOM_TEXT_FIELD1 )
 	VALUES (@lineItem_Id,@salesOrder_id, 0,@lineItem_no, @product_id, @qty, @unit_price, @amount, 
 	@requested_date, CURRENT_TIMESTAMP,'HOLD', @user_id, @user_id, @is_taxable , @po_lineItem_no, CURRENT_TIMESTAMP, @quotation_lineitem_id, @CUSTOMER_PARTNUMBER, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)
 
	/*
	* Whenever a new lineitem added to existing salesorder SO_Status and SO_Is_Pending should update in Sales_Order Table
 	*/
 	UPDATE Sales_Order SET SO_Status = 'PENDING',SO_Is_Pending = 0 WHERE SalesOrder_Id = @salesOrder_id
  
 	/*
  	* Insert the holds in sales Hold table
  	*/
	INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp) 
	VALUES(@lineItem_id, @salesOrder_id, '0','OPERATIONS','SYSTEM',1,CURRENT_TIMESTAMP)
	
	INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp) 
	VALUES(@lineItem_id, @salesOrder_id, '0','MARKETING','SYSTEM',1,CURRENT_TIMESTAMP)
	
	INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp) 
	VALUES(@lineItem_id, @salesOrder_id, '0','SALES','SYSTEM',1,CURRENT_TIMESTAMP)
	
	INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp) 
	VALUES(@lineItem_id, @salesOrder_id, '0','FINANCE','SYSTEM',1,CURRENT_TIMESTAMP)
 
 end
 
 
